package com.sihai.ehr.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.Calendar;

import javax.annotation.Resource;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.stereotype.Service;

import com.sihai.core.CriteriaCreator;
import com.sihai.core.DataAccessor;
import com.sihai.core.DynamicQuery;
import com.sihai.core.Limit;
import com.sihai.core.LimitedList;
import com.sihai.core.QueryCreator;
import com.sihai.core.Sorter;
import com.sihai.core.support.HibernateDataAccessor;
import com.sihai.ehr.model.biz.ExamQualCoerceSet;
import com.sihai.ehr.model.biz.ExamQualDistSet;
import com.sihai.ehr.model.sys.DispersionPatternEnum;
import com.sihai.ehr.model.sys.ExaminePeriodEnum;
import com.sihai.ehr.model.sys.Organization;
import com.sihai.ehr.repository.EmployeeRepository;
import com.sihai.ehr.service.ExamQualSetService;
import com.sihai.ehr.web.pojo.ExamQualCoerceSetPojo;

/**
 * 定性考核强制分布设置服务实现类
 * 
 * @author Henry
 * 
 */
@Service("examQualSetService")
public class ExamQualSetServiceImpl implements ExamQualSetService {

	@Resource
	DataAccessor dataAccessor;

	@Resource
	EmployeeRepository employeeRepository;

	@Override
	public boolean checkScoreAreaExist(final Long organizationId,
			final Float scoreStart, final Float scoreEnd,
			final ExaminePeriodEnum periodType, final Long id) {
		Long existCount = 0L;
		try {
			existCount = dataAccessor.findUnique(Long.class,
					new QueryCreator() {

						@Override
						public Query createQuery(Session session) {
							// TODO Auto-generated method stub
							if (id > 0) {
								return session
										.createQuery("select count(e.id) from ExamQualCoerceSet e where e.cancel<> 1 and e.organization.id="
												+ organizationId
												+ " and (e.scoreStart <="
												+ scoreStart
												+ " and e.scoreEnd >="
												+ scoreStart
												+ " or e.scoreStart <="
												+ scoreEnd
												+ " and e.scoreEnd >="
												+ scoreEnd
												+ ") and e.id<>"
												+ id
												+ " and e.periodType='"
												+ periodType.toString() + "'");
							} else {
								return session
										.createQuery("select count(e.id) from ExamQualCoerceSet e where e.cancel<> 1 and e.organization.id="
												+ organizationId
												+ " and (e.scoreStart <="
												+ scoreStart
												+ " and e.scoreEnd >="
												+ scoreStart
												+ " or e.scoreStart <="
												+ scoreEnd
												+ " and e.scoreEnd >="
												+ scoreEnd
												+ ")"
												+ " and e.periodType='"
												+ periodType.toString() + "'");
							}
						}
					});
		} catch (Exception e) {
			// TODO: handle exception
		}
		if (existCount > 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public ExamQualCoerceSet getExamQualCoerceSet(Long id) {
		if (id == null || id < 0) {
			throw new RuntimeException("【传入参数非法】");
		}
		try {
			return dataAccessor.get(ExamQualCoerceSet.class, id);
		} catch (Exception e) {
			throw new RuntimeException(
					"【异常原因：获取强制分布设置对象数据库发生异常】【异常说明：+"
							+ e.getMessage()
							+ "】【发生地点：ExamQualSetServiceImpl.getExamQualCoerceSet;参数id="
							+ id + " 】");
		}
	}

	@Override
	public ExamQualDistSet getExamQualDistSet(final Long organizationId,
			final ExaminePeriodEnum periodType) {
		if (organizationId == null || organizationId < 0) {
			throw new RuntimeException("【传入参数非法】");
		}
		try {
			return dataAccessor.findUnique(ExamQualDistSet.class,
					new QueryCreator() {

						@Override
						public Query createQuery(Session session) {
							String hql = "from ExamQualDistSet e where e.organization.id=:organizationId and e.periodType=:periodType";
							Query query = session.createQuery(hql);
							query.setParameter("organizationId", organizationId);
							query.setParameter("periodType", periodType);
							return query;
						}
					});
		} catch (Exception e) {
			throw new RuntimeException(
					"【异常原因：获取强制分布设置对象数据库发生异常】【异常说明：+"
							+ e.getMessage()
							+ "】【发生地点：ExamQualSetServiceImpl.getExamQualDistSet;参数organizationId="
							+ organizationId + " 】");
		}
	}

	@Override
	public LimitedList<ExamQualCoerceSet> searchExamQualCoerceSets(
			final Long organizationId, final ExaminePeriodEnum periodType,
			Limit limit, Sorter sorter) {
		if (organizationId <= 0) {
			throw new RuntimeException("【传入参数非法】【organizationId:"
					+ organizationId + "】");
		}
		try {
			return dataAccessor.findByQuery(ExamQualCoerceSet.class,
					new QueryCreator() {

						@Override
						public Query createQuery(Session session) {
							DynamicQuery dynamicQuery = new DynamicQuery(
									"from ExamQualCoerceSet e where e.cancel <> 1");
							dynamicQuery.appendIfNotNull(
									"and e.organization.id =:organization_id",
									"organization_id", organizationId);
							dynamicQuery.appendIfNotNull(
									"and e.periodType =:periodType",
									"periodType", periodType);
							return dynamicQuery.build(session);
						}
					}, limit, sorter);
		} catch (Exception e) {
			throw new RuntimeException(
					"【异常原因：获取强制分布设置集合时数据库发生异常】【异常说明：+"
							+ e.getMessage()
							+ "】【发生地点：ExamQualSetServiceImpl.searchExamQualCoerceSets;参数organizationId="
							+ organizationId + "】");
		}

	}

	@Override
	public LimitedList<ExamQualCoerceSet> searchExamQualCoerceSets(
			final Long organizationId,
			final DispersionPatternEnum dispersionPatternEnum,
			final ExaminePeriodEnum periodEnum, Limit limit, Sorter sorter) {
		if (organizationId <= 0) {
			throw new RuntimeException("【传入参数非法】【organizationId:"
					+ organizationId + "】");
		}
		try {
			LimitedList<ExamQualCoerceSet> limitedList = dataAccessor
					.findByQuery(ExamQualCoerceSet.class, new QueryCreator() {

						@Override
						public Query createQuery(Session session) {
							DynamicQuery dynamicQuery = new DynamicQuery(
									"from ExamQualCoerceSet e where e.cancel <> 1");
							dynamicQuery.appendIfNotNull(
									"and e.organization.id =:organization_id",
									"organization_id", organizationId);
							dynamicQuery.appendIfNotNull("and e.type =:type",
									"type", dispersionPatternEnum);
							dynamicQuery.appendIfNotNull(
									"and e.periodType =:periodType",
									"periodType", periodEnum);
							return dynamicQuery.build(session);
						}
					}, limit, sorter);

			int rawTotalNumber = Integer
					.parseInt(getTotalCountByOrganizationIdAndPeriodType(
							organizationId, periodEnum, true).toString());
			if (limitedList != null && limitedList.size() > 0) {
				if (limitedList.get(0).getType() == DispersionPatternEnum.percentage) {
					// 统计百分比
					for (ExamQualCoerceSet coerceSet : limitedList) {
						coerceSet.setNumber((int) Math.floor((double) coerceSet
								.getPercentage() * rawTotalNumber));
					}
					// 调整人数
					Float hasSetPercentage = 0f;
					int hasSetNubmer = 0;
					for (ExamQualCoerceSet coerceSet : limitedList) {
						hasSetPercentage += coerceSet.getPercentage();
						hasSetNubmer += coerceSet.getNumber();
					}
					if (hasSetPercentage >= 1 && hasSetNubmer < rawTotalNumber) {
						ExamQualCoerceSet vCoerceSet = limitedList
								.get(limitedList.size() / 2
										+ limitedList.size() % 2);
						vCoerceSet.setNumber(vCoerceSet.getNumber()
								+ rawTotalNumber - hasSetNubmer);
					}
				}
			}
			return limitedList;
		} catch (Exception e) {
			throw new RuntimeException(
					"【异常原因：获取强制分布设置集合时数据库发生异常】【异常说明：+"
							+ e.getMessage()
							+ "】【发生地点：ExamQualSetServiceImpl.searchExamQualCoerceSets;参数organizationId="
							+ organizationId + ",dispersionPatternEnum="
							+ dispersionPatternEnum + "】");
		}

	}

	@Override
	public LimitedList<ExamQualCoerceSet> searchExamQualCoerceSets(
			final ExamQualCoerceSet examQualCoerceSet, Limit limit,
			Sorter sorter) {
		if (examQualCoerceSet == null) {
			try {
				return dataAccessor.findByClass(ExamQualCoerceSet.class, limit,
						sorter);
			} catch (Exception e) {
				throw new RuntimeException(
						"【异常原因：根据分布对象获取强制分布数据库发生异常】【发生地点：ExamQualSetServiceImpl.searchExamQualCoerceSets;参数examQualCoerceSet为空】");
			}
		}
		try {
			return dataAccessor.findByQuery(ExamQualCoerceSet.class,
					new QueryCreator() {

						@Override
						public Query createQuery(Session session) {
							DynamicQuery dynamicQuery = new DynamicQuery(
									"from examQualCoerceSet e where e.cancel <> 1");
							dynamicQuery.appendIfNotNull(
									"and e.organization.id =:organization_id",
									"organization_id", examQualCoerceSet
											.getOrganization().getId());
							dynamicQuery.appendIfNotNull("and i.type =:type",
									"type", examQualCoerceSet.getType());
							return dynamicQuery.build(session);
						}
					}, limit, sorter);
		} catch (Exception e) {
			throw new RuntimeException(
					"【异常原因：根据强制分布对象获取强制分布设置集合时数据库发生异常】【异常说明：+"
							+ e.getMessage()
							+ "】【发生地点：ExamQualSetServiceImpl.searchExamQualCoerceSets;参数examQualCoerceSet="
							+ examQualCoerceSet.toString() + "】");
		}

	}

	@Override
	public void saveExamQualCoerceSet(ExamQualCoerceSet examQualCoerceSet) {
		if (examQualCoerceSet == null
				|| examQualCoerceSet.getOrganization() == null) {
			throw new RuntimeException("【examQualCoerceSet对象参数异常】");
		}
		try {
			dataAccessor.saveOrUpdate(examQualCoerceSet);
		} catch (Exception e) {
			throw new RuntimeException("【异常原因：保存强制分布对象到数据库发生异常】【异常说明："
					+ e.getMessage()
					+ "】【发生地点：ExamQualSetServiceImpl.saveExamQualCoerceSet】");
		}
	}

	@Override
	public void saveExamQualDistSet(ExamQualDistSet examQualDistSet) {
		if (examQualDistSet == null
				|| examQualDistSet.getOrganization() == null) {
			throw new RuntimeException("【examQualCoerceSet对象参数异常】");
		}
		try {
			dataAccessor.saveOrUpdate(examQualDistSet);
		} catch (Exception e) {
			throw new RuntimeException("【异常原因：保存强制分布设置对象到数据库发生异常】【异常说明："
					+ e.getMessage()
					+ "】【发生地点：ExamQualSetServiceImpl.saveExamQualDistSet】");
		}
	}

	@Override
	public void deleteExamQualCoerceSets(final Long organizationId,
			final ExaminePeriodEnum periodType) {
		// TODO Auto-generated method stub
		if (organizationId == null || organizationId < 0 || periodType == null) {
			throw new RuntimeException("【参数异常：organizationId或periodType参数不正确】");
		}
		try {
			dataAccessor.batchUpdate(new QueryCreator() {

				@Override
				public Query createQuery(Session session) {
					// TODO Auto-generated method stub
					return session
							.createQuery("delete from ExamQualCoerceSet e where e.organization.id="
									+ organizationId
									+ " and e.periodType='"
									+ periodType + "'");
				}
			});
		} catch (Exception e) {
			throw new RuntimeException("【异常原因：删除考核强制分布设置数据库发生异常】【异常说明："
					+ e.getMessage()
					+ "】【发生地点：ExamQualSetServiceImpl.deleteExamQualCoerceSets】");
		}
	}

	@Override
	public void deleteExamQualCoerceSetsById(final Long id) {
		// TODO Auto-generated method stub
		if (id == null || id < 0) {
			throw new RuntimeException("【参数异常：id参数不正确】");
		}
		try {
			dataAccessor.batchUpdate(new QueryCreator() {

				@Override
				public Query createQuery(Session session) {
					// TODO Auto-generated method stub
					return session
							.createQuery("delete from ExamQualCoerceSet e where e.id="
									+ id);
				}
			});
		} catch (Exception e) {
			throw new RuntimeException("【异常原因：删除考核强制分布设置数据库发生异常】【异常说明："
					+ e.getMessage()
					+ "】【发生地点：ExamQualSetServiceImpl.deleteExamQualCoerceSets】");
		}
	}

	@Override
	public void removeExamQualCoerceSets(final String examQualCoerceSetIds) {
		if (examQualCoerceSetIds == null || examQualCoerceSetIds.isEmpty()) {
			throw new RuntimeException("【参数异常：examQualCoerceSetIds为空】");
		}
		try {
			dataAccessor.batchUpdate(new QueryCreator() {
				@Override
				public Query createQuery(Session session) {
					return session.createQuery(MessageFormat
							.format("delete from ExamQualCoerceSet e where id in ({0})",
									examQualCoerceSetIds));
				}
			});
		} catch (Exception e) {
			throw new RuntimeException("【异常原因：移出考核强制分布设置数据库发生异常】【异常说明："
					+ e.getMessage()
					+ "】【发生地点：ExamQualSetServiceImpl.removeExamQualCoerceSets】");
		}
	}

	@Override
	public LimitedList<ExamQualCoerceSetPojo> queryExamQualSetByQualScore(
			Long orgId, Long dutyId, Long peroidId, ExaminePeriodEnum periodEnum) {
		Calendar mCalendar = Calendar.getInstance();
		// 当前考核
		mCalendar.add(Calendar.MONTH, -1);
		DispersionPatternEnum disPatternEnum = DispersionPatternEnum.no_distribution;
		LimitedList<ExamQualCoerceSetPojo> listCoerceSetPojos = new LimitedList<ExamQualCoerceSetPojo>();
		int rawTotalNumber = 0;
		LimitedList<ExamQualCoerceSet> coerceSets = searchExamQualCoerceSets(
				orgId, null, periodEnum, null, null);
		if (coerceSets == null || coerceSets.size() == 0) {
			return listCoerceSetPojos;
		}
		String strFields = "e.id,e.name,e.number,e.percentage,e.score_start,e.score_end,e.type,e.organization_id,IFNULL(t.Number,0) as actual_number";
		String strStaticsFields = "sum(IFNULL(t.Number,0)) as actual_total,e.type";
		String strSQL = "select {0} " + "from exam_qual_coerce_set as e "
				+ "left outer join (select case ";
		for (ExamQualCoerceSet examQualCoerceSet : coerceSets) {
			strSQL += " when score>=" + examQualCoerceSet.getScoreStart()
					+ " and score<=" + examQualCoerceSet.getScoreEnd()
					+ " then '" + examQualCoerceSet.getName() + "'";

			rawTotalNumber += examQualCoerceSet.getNumber();
		}
		strSQL += " end as NameTag,count(1) as Number from exam_qual_score as st,exam_period as p,employeeinfo_work as w,organization as o where st.period_id=p.id and st.work_id=w.id and p.period_type='"
				+ periodEnum.toString()
				+ "'"
				+ " and w.organization_id=o.id and instr(o.tree_path,',"
				+ orgId + ",')>0";
		if (peroidId > 0) {
			strSQL += " and st.period_id=" + peroidId;
		} else {
			// 当前考核周期，根据当前时间去检测
			strSQL += " and p.exam_year=" + mCalendar.get(Calendar.YEAR)
					+ " and p.exam_monthly="
					+ (mCalendar.get(Calendar.MONTH) + 1);
		}
		strSQL += " group by NameTag) as t "
				+ "on t.NameTag=e.name where e.cancel<>1 and organization_id="
				+ orgId + " and e.period_type='" + periodEnum.toString() + "'";

		Session vSession = ((HibernateDataAccessor) dataAccessor)
				.getHibernateTemplate().getSessionFactory().getCurrentSession();
		Connection vConnection = vSession.connection();
		Statement vStatement;
		int actualTotalNumber = 0;
		try {
			vStatement = vConnection.createStatement();

			// 统计总数
			ResultSet rsTotal = vStatement.executeQuery(strSQL.replace("{0}",
					strStaticsFields));
			if (rsTotal.next()) {
				actualTotalNumber = rsTotal.getInt("actual_total");
				disPatternEnum = DispersionPatternEnum.valueOf(rsTotal
						.getString("type"));
			}
			// 如果类型为百分比重新获取实际人数
			if (disPatternEnum == DispersionPatternEnum.percentage) {
				rawTotalNumber = Integer
						.parseInt(getTotalCountByOrganizationIdAndPeriodType(
								orgId, periodEnum, true).toString());
			}
			rsTotal.close();
			ResultSet rs = vStatement.executeQuery(strSQL.replace("{0}",
					strFields));
			while (rs.next()) {
				ExamQualCoerceSetPojo mCoerceSetPojo = new ExamQualCoerceSetPojo(
						rs.getLong("id"), rs.getLong("organization_id"),
						rs.getString("type"), rs.getString("name"),
						rs.getFloat("score_start"), rs.getFloat("score_end"),
						rs.getFloat("percentage"), rs.getInt("number"),
						rs.getInt("actual_number"), rawTotalNumber,
						actualTotalNumber);
				listCoerceSetPojos.add(mCoerceSetPojo);
			}
			rs.close();
			vStatement.close();
			vConnection.close();

			// 调用人数
			if (listCoerceSetPojos != null && listCoerceSetPojos.size() > 0) {
				Float hasSetPercentage = 0f;
				int hasSetNubmer = 0;
				for (ExamQualCoerceSetPojo coerceSet : listCoerceSetPojos) {
					hasSetPercentage += coerceSet.getPercentage();
					hasSetNubmer += coerceSet.getNumber();
				}
				if (hasSetPercentage >= 1 && hasSetNubmer < rawTotalNumber) {
					ExamQualCoerceSetPojo vCoerceSet = listCoerceSetPojos
							.get(listCoerceSetPojos.size() / 2
									+ listCoerceSetPojos.size() % 2);
					vCoerceSet.setNumber(vCoerceSet.getNumber()
							+ rawTotalNumber - hasSetNubmer);
					vCoerceSet.setBalanceNumber(vCoerceSet.getActualNumber()
							- vCoerceSet.getNumber());
				}
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return listCoerceSetPojos;
	}
	
	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * com.sihai.ehr.service.ExamQualSetService#queryExamQualSetByStatics(java
	 * .lang.Long, java.lang.Long,
	 * com.sihai.ehr.model.sys.DispersionPatternEnum, int)
	 */
	@Override
	public LimitedList<ExamQualCoerceSetPojo> queryExamQualSetByStatics(
			Long orgId, Long dutyId, Long peroidId, ExaminePeriodEnum periodEnum) {
		/*
		 * select sum(IFNULL(t.Number,0)) as actual_total,e.type from
		 * exam_qual_coerce_set as e left outer join ( select case when
		 * qual_score>=95.0 and qual_score<=100.0 then '特优' when
		 * qual_score>=85.0 and qual_score<=94.9 then '优等' when qual_score>=75.0
		 * and qual_score<=84.9 then '甲等' when qual_score>=65.0 and
		 * qual_score<=74.9 then '乙上' when qual_score>=0.0 and qual_score<=64.9
		 * then '乙等' end as NameTag,count(1) as Number from exam_statistics as
		 * st,exam_period as p where st.period_id=p.id group by NameTag ) as t
		 * on t.NameTag=e.name where e.cancel<>1 and organization_id=9
		 */
		Calendar mCalendar = Calendar.getInstance();
		// 当前考核
		mCalendar.add(Calendar.MONTH, -1);
		DispersionPatternEnum disPatternEnum = DispersionPatternEnum.no_distribution;
		LimitedList<ExamQualCoerceSetPojo> listCoerceSetPojos = new LimitedList<ExamQualCoerceSetPojo>();
		int rawTotalNumber = 0;
		LimitedList<ExamQualCoerceSet> coerceSets = searchExamQualCoerceSets(
				orgId, null, periodEnum, null, null);
		if (coerceSets == null || coerceSets.size() == 0) {
			return listCoerceSetPojos;
		}
		String strFields = "e.id,e.name,e.number,e.percentage,e.score_start,e.score_end,e.type,e.organization_id,IFNULL(t.Number,0) as actual_number";
		String strStaticsFields = "sum(IFNULL(t.Number,0)) as actual_total,e.type";
		String strSQL = "select {0} " + "from exam_qual_coerce_set as e "
				+ "left outer join (select case ";
		for (ExamQualCoerceSet examQualCoerceSet : coerceSets) {
			strSQL += " when qual_score>=" + examQualCoerceSet.getScoreStart()
					+ " and qual_score<=" + examQualCoerceSet.getScoreEnd()
					+ " then '" + examQualCoerceSet.getName() + "'";

			rawTotalNumber += examQualCoerceSet.getNumber();
		}
		strSQL += " end as NameTag,count(1) as Number from exam_statistics as st,exam_period as p,employeeinfo_work as w,organization as o where st.period_id=p.id and st.work_id=w.id and p.period_type='"
				+ periodEnum.toString()
				+ "'"
				+ " and w.organization_id=o.id and instr(o.tree_path,',"
				+ orgId + ",')>0";
		if (peroidId > 0) {
			strSQL += " and st.period_id=" + peroidId;
		} else {
			// 当前考核周期，根据当前时间去检测
			strSQL += " and p.exam_year=" + mCalendar.get(Calendar.YEAR)
					+ " and p.exam_monthly="
					+ (mCalendar.get(Calendar.MONTH) + 1);
		}
		strSQL += " group by NameTag) as t "
				+ "on t.NameTag=e.name where e.cancel<>1 and organization_id="
				+ orgId + " and e.period_type='" + periodEnum.toString() + "'";

		Session vSession = ((HibernateDataAccessor) dataAccessor)
				.getHibernateTemplate().getSessionFactory().getCurrentSession();
		Connection vConnection = vSession.connection();
		Statement vStatement;
		int actualTotalNumber = 0;
		/**
		 * if (limitedList != null && limitedList.size() > 0) { if
		 * (limitedList.get(0).getType() == DispersionPatternEnum.percentage) {
		 * // 统计百分比 for (ExamQualCoerceSet coerceSet : limitedList) {
		 * coerceSet.setNumber((int) Math.floor((double) coerceSet
		 * .getPercentage() * rawTotalNumber)); } // 调整人数 Float hasSetPercentage
		 * = 0f; int hasSetNubmer = 0; for (ExamQualCoerceSet coerceSet :
		 * limitedList) { hasSetPercentage += coerceSet.getPercentage();
		 * hasSetNubmer += coerceSet.getNumber(); } if (hasSetPercentage >= 1 &&
		 * hasSetNubmer < rawTotalNumber) { ExamQualCoerceSet vCoerceSet =
		 * limitedList .get(limitedList.size() / 2 + limitedList.size() % 2);
		 * vCoerceSet.setNumber(vCoerceSet.getNumber() + rawTotalNumber -
		 * hasSetNubmer); } } }
		 */
		try {
			vStatement = vConnection.createStatement();

			// 统计总数
			ResultSet rsTotal = vStatement.executeQuery(strSQL.replace("{0}",
					strStaticsFields));
			if (rsTotal.next()) {
				actualTotalNumber = rsTotal.getInt("actual_total");
				disPatternEnum = DispersionPatternEnum.valueOf(rsTotal
						.getString("type"));
			}
			// 如果类型为百分比重新获取实际人数
			if (disPatternEnum == DispersionPatternEnum.percentage) {
				rawTotalNumber = Integer
						.parseInt(getTotalCountByOrganizationIdAndPeriodType(
								orgId, periodEnum, true).toString());
			}
			rsTotal.close();
			ResultSet rs = vStatement.executeQuery(strSQL.replace("{0}",
					strFields));
			while (rs.next()) {
				ExamQualCoerceSetPojo mCoerceSetPojo = new ExamQualCoerceSetPojo(
						rs.getLong("id"), rs.getLong("organization_id"),
						rs.getString("type"), rs.getString("name"),
						rs.getFloat("score_start"), rs.getFloat("score_end"),
						rs.getFloat("percentage"), rs.getInt("number"),
						rs.getInt("actual_number"), rawTotalNumber,
						actualTotalNumber);
				listCoerceSetPojos.add(mCoerceSetPojo);
			}
			rs.close();
			vStatement.close();
			vConnection.close();

			// 调用人数
			if (listCoerceSetPojos != null && listCoerceSetPojos.size() > 0) {
				Float hasSetPercentage = 0f;
				int hasSetNubmer = 0;
				for (ExamQualCoerceSetPojo coerceSet : listCoerceSetPojos) {
					hasSetPercentage += coerceSet.getPercentage();
					hasSetNubmer += coerceSet.getNumber();
				}
				if (hasSetPercentage >= 1 && hasSetNubmer < rawTotalNumber) {
					ExamQualCoerceSetPojo vCoerceSet = listCoerceSetPojos
							.get(listCoerceSetPojos.size() / 2
									+ listCoerceSetPojos.size() % 2);
					vCoerceSet.setNumber(vCoerceSet.getNumber()
							+ rawTotalNumber - hasSetNubmer);
					vCoerceSet.setBalanceNumber(vCoerceSet.getActualNumber()
							- vCoerceSet.getNumber());
				}
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return listCoerceSetPojos;
	}

	@Override
	public Long getTotalCountByOrganizationIdAndPeriodType(final Long orgId,
			final ExaminePeriodEnum periodEnum, boolean isReadChild) {
		if (orgId > 0) {
			try {
				if (isReadChild) {
					String treepath = dataAccessor.get(Organization.class,
							orgId).getTreePath();
					final String orgRealtion = treepath;

					Object mObject = dataAccessor.findUnique(Object.class,
							new QueryCreator() {
								@Override
								public Query createQuery(Session session) {
									/*
									 * return session.createQuery(
									 * "select count(o.id) from EmployeeinfoWork o where o.organization.treePath like '"
									 * + orgRealtion + "%'");
									 */
									return session
											.createSQLQuery("select count(e.id) from employeeinfo_work as e where duty_id in ("
													+ "select duty_id from exam_base_config where period_type='"
													+ periodEnum.toString()
													+ "' and duty_id in ("
													+ "select duty.id from duty,organization where duty.organization_id=organization.id and instr(organization.tree_path,'"
													+ orgRealtion + "') > 0))");
								}
							});
					if (mObject != null) {
						return Long.parseLong(mObject.toString());
					}
					return 0L;

				} else {
					Object mObject = this.dataAccessor.findUnique(Long.class,
							new QueryCreator() {
								@Override
								public Query createQuery(Session session) {
									/*
									 * return session.createQuery(
									 * "select count(o.id) from EmployeeinfoWork o where o.organization.id="
									 * + orgId);
									 */
									return session
											.createSQLQuery("select count(e.id) from employeeinfo_work as e where duty_id in ("
													+ "select duty_id from exam_base_config where period_type='"
													+ periodEnum.toString()
													+ "' and duty_id in ("
													+ "select duty.id from duty,organization where duty.organization_id=organization.id and organization.id="
													+ orgId + ") > 0))");
								}
							});

					if (mObject != null) {
						return Long.parseLong(mObject.toString());
					}
					return 0L;
				}
			} catch (Exception e) {
				throw new RuntimeException(
						"【统计部门人数异常】【异常原因：:"
								+ e.getMessage()
								+ "】【发生地点：EmployeeServiceImpl.getTotalCountByOrganizationId】");
			}

		} else {
			return 0L;
		}
	}

	@Override
	public Float getQualCoerceTotalPercentageByOrgId(final Long orgId,
			final ExaminePeriodEnum periodEnum) {

		// 得到待删除的分布总比例 exam_quan_item_set
		Double itemSum = dataAccessor.findUnique(Double.class,
				new QueryCreator() {
					@Override
					public Query createQuery(Session session) {
						return session.createQuery(String
								.format("select sum(e.percentage) from ExamQualCoerceSet e where e.organization.id=%d and e.periodType='"
										+ periodEnum.toString()
										+ "' and e.cancel<>1", orgId));
					}
				});
		if (itemSum != null)
			return itemSum.floatValue();
		else
			return 0f;

	}

}
